# Chapter 14. Java and JDBC

#### Contents:

What Is JDBC?
Simple Database Access
Dynamic Database Access
A Guest Book Servlet

In Chapter 13, "C and C++", we introduced you to the C APIs for MySQL and mSQL. Unfortunately, each API only enables you to program for the database it supports. If you are looking to port an application between MySQL and mSQL, or even worse, if you are looking to make an application work on Oracle or Sybase or any other database engine, you must rewrite your database code to make use of that database engine's proprietary API. Java programmers, however, are mostly freed from database portability issues. They have a single API, the Java DataBase Connectivity API (JDBC), that provides them with a unified interface into all SQL databases.

Because JDBC is a single interface to all databases, you need only to learn it in order to be able to write applications that run on both MySQL and mSQL. In fact, as long as you write proper JDBC code, the Java applications you write will be able to run against any database engine. If you have access to a database other than MySQL or mSQL, you should give this claim a test by running this chapter's examples on that database.

In this chapter, we are assuming a basic understanding of the Java programming language and Java concepts. If you do not already have this background, we strongly recommend taking a look at Exploring Java (O'Reilly & Associates, Inc.). For more details on how to build the sort of three-tier database applications we discussed in Chapter 8, "Database Application Architectures", take a look at Database Programming with JDBC and Java (O'Reilly & Associates, Inc.).

## 14.1. What Is JDBC?

Like all Java APIs, JDBC is a set of classes and interfaces that work together to support a specific set of functionality. In the case of JDBC, this functionality is naturally database access. The classes and interfaces that make up the JDBC API are thus abstractions from concepts common to database access for any kind of database. A `Connection` , for example, is a Java interface representing a database connection. Similarly, a `ResultSet` represents a result set of data returned from a SQL `SELECT` statement. Java puts the classes that form the JDBC API together in the `java.sql` package which Sun introduced in JDK 1.1.

The details of database access naturally differ from vendor to vendor. JDBC does not actually deal with those details. Most of the classes in the `java.sql` package are in fact interfaces -- and thus no implementation details. Individual database vendors provide implementations of these interfaces in the form of something called a JDBC driver. As a database programmer, however, you need to know only a few details about the driver you are using -- the rest you manage via the JDBC interfaces. The vendor specific information you need in order to use JDBC includes:

- The JDBC URL for the driver

- The name of the class that implements`java.sql.Driver`

> #### NOTE

> The new JDBC 2.0 specification adds an optional standard extension API for vendors to implement. If your JDBC vendor implements this standard extension, you do not even need to know the JDBC URL or `Driver` class implementation. It prescribes a `DataSource` class that you can look up by a configurable name in a JNDI[22]-supported directory.

> > [22]JNDI is the Java Naming and Directory Interface API. It lets you store Java objects in a naming and directory service like an Lightweight Directory Access Protocol (LDAP) server and then look them up by name.

Both of these items can be supplied at runtime, either on the command line or in a properties file. Your code never needs to mention these two implementation-dependent pieces. We will cover what the JDBC URL and `Driver` class do in a few paragraphs when we cover database connections. Figure 14-1 diagrams the interfaces of JDBC.

#### Figure 14-1. The classes and interfaces of the JDBC API

### 14.1.1. The Database Connection

Your first step is to connect to the database. One of the few implementation classes in the `java.sql.package` is the `DriverManager` class. It maintains a list of JDBC implementations and provides you with database connections based on JDBC URLs you provide it. A JDBC URL comes in the form of jdbc:protocol:subprotocol. It tells a `DriverManager` which database engine you wish to connect to and it provides the `DriverManager` with enough information to make a connection.

> #### NOTE

> JDBC uses the word "driver" in multiple contexts. In the lower-case sense, a JDBC driver is the collection of classes that together implement all of the JDBC interfaces and provide an application with access to at least one database. In the upper-case sense, the `Driver` is the class that implements `java.sql.Driver`. Finally, JDBC provides a `DriverManager` that can be used to keep track of all of the different `Driver` implementations.

The protocol part of the URL refers to a given JDBC driver. In the case of MySQL and mSQL, the protocol is mysql and msql, respectively. The subprotocol provides the implementation-specific connection data. Both MySQL and mSQL require a host name and database name in order to make a connection. Optionally, they may require a port if your database engine is not running as root. The full mSQL URL therefore looks like: jdbc:msql://athens.imaginary.com:1114/test. It says that the `DriverManager` should find the mSQL JDBC driver and connect to the database test at athens.imaginary.com on port 1114. All of this is done via a single call to the `DriverManager` `getConnection()` method. Example 14-1 shows how to make a connection to an mSQL database.

#### Example 14-1. A Code Snippet from the Examples that Come with the Imaginary JDBC Driver for mSQL Showing How to Make a Connection

>     import java.sql.*;

>     public class Connect {
>         public static void main(String argv[]) {
>             Connection con = null;

>             try {
>                 // here is the JDBC URL for this database
>                 String url = "jdbc:msql://athens.imaginary.com:1114/db_test";
>                 // more on what the Statement and ResultSet classes do later
>                 Statement stmt;
>                 ResultSet rs;

>                 // either pass this as a property, i.e.
>                 // -Djdbc.drivers=com.imaginary.sql.msql.MsqlDriver
>                 // or load it here like we are doing in this example
>                 Class.forName("com.imaginary.sql.msql.MsqlDriver");
>                 // here is where the connection is made
>                 con = DriverManager.getConnection(url, "borg", "");
>             }
>             catch( SQLException e ) {
>                 e.printStackTrace();
>             }
>             finally {
>                 if( con != null ) {
>                     try { con.close(); }
>                     catch( Exception e ) { }
>                 }
>             }
>         }
>     }

The line `con = DriverManager.getConnection(url, "borg", "")` makes the database connection in this example. In this case, the JDBC URL and `Driver` implementation class names are actually hard coded into this application. The only reason this is acceptable is because this application is a demo for the mSQL-JDBC driver. For a serious application, you would want to load this information from a properties file, pass it as command line arguments, or pass it as system properties. The `Driver` implementation will automatically be loaded if you pass it as the system property jdbc.drivers -- in other words, you do not have to call `Class.forName().newInstance(driver_name)` when you pass the driver name as the jdbc.drivers system property. The second and third arguments to `getConnection()` are the user ID and password to use for the connection. Because mSQL does not use passwords for user authentication, this example just uses an empty string. In MySQL, however, you will need to provide a password.[23]

> [23]MySQL actually has several JDBC drivers. At least one of them allows you to specify the user ID and password as part of the URL.

### 14.1.2. Maintaining Portability Using Properties Files

Though our focus is on two specific databases, it is good Java programming practice to make your applications completely portable. To most people, portability means that you do not write code that will run on only one platform. In the Java world, however, the word "portable" is a much stronger term. It means no hardware resource dependencies, and that means no database dependencies.

We discussed how the JDBC URL and `Driver` name are implementation dependent, but we did not discuss how to avoid hard coding them. Because both are simple strings, you can pass them on the command line as runtime arguments or as parameters to applets. While that solution works, it is hardly elegant since it requires command line users to remember long command lines. A similar solution might be to prompt the user for this information; but again, you are requiring that the user remember a JDBC URL and a Java class name each time they run an application.

A more elegant solution than either of the above solutions would be to use a properties file. Properties files are supported by the `java.util.ResourceBundle` and its subclasses to enable an application to extract runtime specific information from a text file. For a JDBC application, you can stick the URL and `Driver` name in the properties file, leaving the details of the connectivity up to an application administrator. Example 14-1 shows a properties file that provides connection information.

#### Example 14-1. The SelectResource.properties File with Connection Details for a Connection

>     Driver=com.imaginary.sql.msql.MsqlDriver
>     URL=jdbc:msql://athens.imaginary.com:1114/db_test

Example 14-2 shows the portable `Select` class.

#### Example 14-2. Specific Information

>     import java.sql.*;
>     import java.util.*;

>     public class Connect {
>         public static void main(String argv[]) {
>             Connection con = null;
>             ResourceBundle bundle = ResourceBundle.getBundle("SelectResource");

>             try {
>                 String url = bundle.getString("URL");
>                 Statement stmt;
>                 ResultSet rs;

>                  Class.forName(bundle.getString("Driver"));
>                 // here is where the connection is made
>                 con = DriverManager.getConnection(url, "borg", "");
>             }
>             catch( SQLException e ) {
>                 e.printStackTrace();
>             }
>             finally {
>                 if( con != null ) {
>                     try { con.close(); }
>                     catch( Exception e ) { }
>                 }
>             }
>         }
>     }

We have gotten rid of anything specific to mSQL in the sample connection code. One important issue still faces portable JDBC developers -- one that stings mSQL developers in particular. JDBC requires any driver to support SQL2 entry level. This is an ANSI standard for minimum SQL support. As long as you use SQL2 entry level SQL in your JDBC calls, your application will be 100% portable to other database engines. Unfortunately, while MySQL is SQL2 entry level, mSQL is not. Applications you write for mSQL will very likely port to other databases without issue, but applications written to use the full range of SQL92 entry level will not port back to mSQL without pain.

Copyright © 2001 O'Reilly & Associates. All rights reserved.